﻿Option Explicit On
Imports System.Drawing.Drawing2D
Imports System.IO
Imports System.Media
Imports System.Text
Imports DevComponents.DotNetBar.Controls
Imports DevComponents.DotNetBar.SuperGrid
Imports DevComponents.DotNetBar.SuperGrid.Style
Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading
Imports DevComponents.DotNetBar
Public Class frmsearch_allctn
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1

    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim inbtIndex As Integer

    Dim mySqlCommand1 As New MySqlCommand
    Dim mySqlAdaptor1 As New MySqlDataAdapter
    Dim mySqlReader1 As MySqlDataReader

    Dim respone As Object
    Dim idvoyn As String
    Dim idvoys As String
    Public Shared idbooking As String
    Public Shared idborrow As String

    Dim idcontainer As String


    Dim ctnname As String
    Dim typectn As String

    Dim textDate As String
    Dim stringDate() As String
    Dim stringhour() As String
    Dim sumtxt As String
    Dim sumint As Double
    Dim agentline As String

    Dim sumctn40 As Integer = 0
    Dim sumctn20 As Integer = 0
    Dim datecheck As String
    Dim stringDate1() As String

    Private Sub frmsearch_allctn_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        searhctn()




    End Sub
    Public Sub searhctn()
        Dim voyn As String = ""
        Dim voys As String = ""
        Dim booking As String = ""
        Dim forwarder As String = ""
        Dim ctn As String = ""
        Dim seal As String = ""
        Dim agent As String = ""
        Dim trans As String = ""
        Dim nocar As String = ""
        Dim weight As String = ""
        Dim dateout As String = ""
        Dim hhmmout As String = ""
        Dim hhmmin As String = ""
        Dim datein As String = ""
        Dim shipper As String = ""
        mysql.Close()
        Dim statctn As String


        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        'mySqlCommand.CommandText = "SELECT CTNVOYN,CTNVOYS,BNO,BNFORWARDERNAME,BNSHIPNAME,CTNSTRING,CTNSEALID,CTNAGENT,COMNAME,CARID,CTNWEIGHT,TIMEDATEIN,TIMEHHMMIN,TIMEHHMM,TIMEDATE,CTNSTAT FROM ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID left join borrow on borrow.CTNID = ctnmain.CTNMAINID left join booking on booking.BOOKINGID = borrow.BOOKID where CTNSTRING like '%" & TextBoxX1.Text & "%' or CTNSEALID like '%" & TextBoxX1.Text & "%' or CTNVOYS like '%" & TextBoxX1.Text & "%' limit 100;"
        mySqlCommand.CommandText = "call search_ctn('" & TextBoxX1.Text & "')"
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand


        Try
            mySqlReader = mySqlCommand.ExecuteReader
            'ListView2.Items.Clear()
            DataGridViewX1.Rows.Clear()
            While (mySqlReader.Read())
                If mySqlReader("CTNVOYN") Is DBNull.Value Then
                    voyn = ""
                Else
                    voyn = mySqlReader("CTNVOYN")
                End If
                If mySqlReader("CTNVOYS") Is DBNull.Value Then
                    voys = ""
                Else
                    voys = mySqlReader("CTNVOYS")
                End If
                If mySqlReader("BNO") Is DBNull.Value Then
                    booking = ""
                Else
                    booking = mySqlReader("BNO")
                End If
                If mySqlReader("BNFORWARDERNAME") Is DBNull.Value Then
                    forwarder = ""
                Else
                    forwarder = mySqlReader("BNFORWARDERNAME")
                End If
                If mySqlReader("BNSHIPNAME") Is DBNull.Value Then
                    shipper = ""
                Else
                    shipper = mySqlReader("BNSHIPNAME")
                End If


                If mySqlReader("CTNSTRING") Is DBNull.Value Then
                    ctn = ""
                Else
                    ctn = mySqlReader("CTNSTRING")
                End If


                If mySqlReader("CTNSEALID") Is DBNull.Value Then
                    seal = ""
                Else
                    seal = mySqlReader("CTNSEALID")
                End If
                If mySqlReader("CTNAGENT") Is DBNull.Value Then
                    agent = ""
                Else
                    agent = mySqlReader("CTNAGENT")
                End If
                If mySqlReader("COMNAME") Is DBNull.Value Then
                    trans = ""
                Else
                    trans = mySqlReader("COMNAME")
                End If
                If mySqlReader("CARID") Is DBNull.Value Then
                    nocar = ""
                Else
                    nocar = mySqlReader("CARID")
                End If
                If mySqlReader("CTNWEIGHT") Is DBNull.Value Then
                    weight = ""
                Else
                    weight = mySqlReader("CTNWEIGHT")
                End If
                If mySqlReader("TIMEDATEIN") Is DBNull.Value Then
                    datein = ""
                Else
                    datein = mySqlReader("TIMEDATEIN")
                End If
                If mySqlReader("TIMEHHMMIN") Is DBNull.Value Then
                    hhmmin = ""
                Else
                    hhmmin = mySqlReader("TIMEHHMMIN")
                End If
                If mySqlReader("TIMEDATE") Is DBNull.Value Then
                    dateout = ""
                Else
                    dateout = mySqlReader("TIMEDATE")
                End If
                If mySqlReader("TIMEHHMM") Is DBNull.Value Then
                    hhmmout = ""
                Else
                    hhmmout = mySqlReader("TIMEHHMM")
                End If
                'With ListView2.Items

                '    With .Add(mySqlReader("CTNSTRING"))
                '        If mySqlReader("CTNSEALID") Is DBNull.Value Then
                '            .SubItems.Add("-")
                '        Else

                '            .SubItems.Add(mySqlReader("CTNSEALID"))

                '        End If

                '        .SubItems.Add(mySqlReader("CTNSIZE"))
                '        .SubItems.Add(mySqlReader("CTNAGENT"))
                If mySqlReader("CTNSTAT") = "2" Then
                    statctn = "MS"

                ElseIf mySqlReader("CTNSTAT") = "3" Then
                    statctn = "FL"

                ElseIf mySqlReader("CTNSTAT") = "4" Then
                    statctn = "ST"

                ElseIf mySqlReader("CTNSTAT") = "1" Then
                    statctn = "MT"

                Else
                    statctn = ""
                End If
                Dim check1 As Boolean
                Dim check As Boolean
           


                '        .SubItems.Add(mySqlReader("idborrow"))
                '        .SubItems.Add(mySqlReader("CTNMAINID"))
                '        datecheck = mySqlReader("VOYDATEEN")
                '        stringDate1 = Split(datecheck, "-")
                '        .SubItems.Add(stringDate1(2) + stringDate1(1) + stringDate1(0))
                '    End With
    
                DataGridViewX1.Rows.Add({voyn, voys, booking, forwarder, shipper, ctn, seal, agent, trans, nocar, weight, dateout, hhmmout, datein, hhmmin, statctn})



                'End With



            End While


            'For i As Integer = 0 To DataGridViewX1.Rows.Count - 1

            '    If DataGridViewX1.Rows(i).Cells(10).Value = True And DataGridViewX1.Rows(i).Cells(13).Value = False Then

            '        'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

            '        'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

            '        DataGridViewX1.Rows(i).Cells(14).Style.BackColor = Color.DarkOrange
            '    ElseIf DataGridViewX1.Rows(i).Cells(10).Value = True And DataGridViewX1.Rows(i).Cells(13).Value = True Then

            '        DataGridViewX1.Rows(i).Cells(14).Style.BackColor = Color.DeepSkyBlue

            '    End If

            'Next

            'Dim resultd As Integer
            'ListView2.View = View.Details
            'ListView2.GridLines = True
            'For i As Integer = 0 To ListView2.Items.Count - 1 Step 1 'Code การสลับสีใน listview
            '    resultd = CInt(Date.Now.ToString("yyyyMMdd")) - CInt(ListView2.Items(i).SubItems(7).Text)
            '    If resultd > 8 Then
            '        ListView2.Items(i).BackColor = Color.Red
            '    Else
            '        ListView2.Items(i).BackColor = Color.White
            '    End If
            'Next i
            mysql.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        searhctn()
    End Sub

    Private Sub TextBoxX1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxX1.KeyDown
        If e.KeyCode = Keys.Enter Then

            searhctn()
        End If
    End Sub
End Class